﻿using System;
using System.Collections.Generic;
using System.Collections;
using System.Collections.ObjectModel;
using System.Linq;
using System.Text;
using Utilities;
using System.Linq.Expressions;

namespace PayexpDAL
{
    public sealed class DataAccessHandler
    {

        //public bool IsUserValid(string userName, string base64EncodedPassword, AlfaRomeoEntities context)
        //{
        //    if (context.PayexpThickClientUsers.Where(t => t.UserName.ToLower().Trim().Equals(userName.ToLower().Trim()) &&
        //                                t.Password.Equals(base64EncodedPassword)).Count() == 1)
        //    {
        //        return true;
        //    }
        //    else
        //    {
        //        return false;
        //    }
        //}

        public PayexpThickClientUser IsUserValid(string userName, string base64EncodedPassword, AlfaRomeoEntities context)
        {
            PayexpThickClientUser loggedInUser = context.PayexpThickClientUsers.Where(t => t.UserName.ToLower().Trim().Equals(userName.ToLower().Trim()) &&
                                        t.Password.Equals(base64EncodedPassword)).FirstOrDefault();

            return loggedInUser;
        }

        public bool AddUser(string userName, string base64EncodedPassword, AlfaRomeoEntities context)
        {
            string lowerCaseUserName = userName.ToLower().Trim();
            if (context.PayexpThickClientUsers.Any(t => t.UserName.Equals(lowerCaseUserName)))
            {
                throw new DALException(string.Format("User {0} already exists.", userName));
            }
            PayexpThickClientUser user = new PayexpThickClientUser();
            user.UserName = lowerCaseUserName;
            user.Password = base64EncodedPassword;
            context.PayexpThickClientUsers.AddObject(user);
            context.SaveChanges();
            return true;
        }

        public List<string> GetRegisteredUserNames(AlfaRomeoEntities context)
        {
            return context.PayexpThickClientUsers.Select(t => t.UserName.ToLower()).ToList();
        }

        public List<TransactionType> GetAllTransactionTypeList(AlfaRomeoEntities context)
        {
            var transactionTypeList = context.TransactionTypes.Select(t => t).ToList();
            //transactionTypeList.ForEach(t => t.LoadParentHierarchyList(context));
            return transactionTypeList;
        }

        public List<TransactionType> GetTransactionTypeList(int parentId, AlfaRomeoEntities context)
        {
            var transactionTypeList = context.TransactionTypes.Where(t => t.ParentId == parentId).ToList();
            //transactionTypeList.ForEach(t => t.LoadParentHierarchyList(context));
            return transactionTypeList;
        }

        public List<TransactionType> GetTransactionTypeListByIdList(List<int> idList, AlfaRomeoEntities context)
        {
            var transactionTypeList = context.TransactionTypes.Where(t => idList.Contains(t.Id)).ToList();
            //transactionTypeList.ForEach(t => t.LoadParentHierarchyList(context));
            return transactionTypeList;
        }

        public List<TransactionMaster> GetTransactionMasterListByIdList(List<int> idList, AlfaRomeoEntities context)
        {
            List<TransactionMaster> transactionMasterList = context.TransactionMasters.Include("TransactionMasterChequeDetails")
                                                 .Where(t => idList.Contains(t.Id) && t.IsActive).OrderByDescending(t => t.TransactionDate).ToList();

            RemoveDeletedChildEntriesFromTransactionMaster(transactionMasterList);
            return transactionMasterList;
        }

        public List<TransactionMasterHistory> GetTransactionMasterHistoryByTransactionMasterId(int transctionMasterId, AlfaRomeoEntities context)
        {
            List<TransactionMasterHistory> requiredHistoryList = context.TransactionMasterHistories.Include("TransactionMasterChequeDetailsHistories")
                                                 .Where(t => t.TransactionMasterId.Equals(transctionMasterId)).OrderByDescending(t => t.UpdatedDate).ToList();

            for (int versionNumber = requiredHistoryList.Count; versionNumber >= 1; versionNumber--)
            {
                requiredHistoryList[requiredHistoryList.Count - versionNumber].VersionNumer = versionNumber;
            }
            return requiredHistoryList;
        }

        public List<HeadDefaultValue> GetAllHeadDefaultValues(AlfaRomeoEntities context)
        {
            return context.HeadDefaultValues.Where(t => t.IsActive).ToList();
        }

        public List<ApplicationSetting> GetAllApplicationSettings(AlfaRomeoEntities context)
        {
            return context.ApplicationSettings.Where(t => t.IsActive).ToList();
        }

        public void UpdateApplicationSettings(List<ApplicationSetting> applicationSettingList, AlfaRomeoEntities context)
        {
            List<ApplicationSetting> existingSettingList = GetAllApplicationSettings(context);
            foreach (ApplicationSetting setting in existingSettingList)
            {
                ApplicationSetting newSetting = applicationSettingList.Where(t => t.Id.Equals(setting.Id)).FirstOrDefault();
                if (newSetting != null)
                {
                    setting.SettingValue = newSetting.SettingValue;
                    setting.UpdatedBy = newSetting.UpdatedBy;
                    setting.UpdatedDate = newSetting.UpdatedDate;
                }
            }

            context.SaveChanges();
            SettingsAndStaticListLoader.LoadAppSettings();
        }

        public void InsertOrUpdateHeadDefaultValues(List<TransactionType> leafNodeList, HeadDefaultValue newDefaultValue,
                                                        AlfaRomeoEntities context)
        {
            List<int> leafNodeIdList = leafNodeList.Select(t => t.Id).ToList();
            List<HeadDefaultValue> existingDefaultValueList = context.HeadDefaultValues.Where(t => t.IsActive && leafNodeIdList.Contains(t.TransactionTypeId)).ToList();

            foreach (TransactionType leafNode in leafNodeList)
            {
                HeadDefaultValue existingDefaultValue = existingDefaultValueList.Where(t => t.TransactionTypeId.Equals(leafNode.Id)).FirstOrDefault();
                if (existingDefaultValue != null)
                {
                    existingDefaultValue.TransactionModeValue = newDefaultValue.TransactionModeValue;
                    existingDefaultValue.BankName = newDefaultValue.BankName;
                    existingDefaultValue.Amount = newDefaultValue.Amount;
                    existingDefaultValue.Narration = newDefaultValue.Narration;
                    existingDefaultValue.IsAutoApproved = newDefaultValue.IsAutoApproved;
                    existingDefaultValue.ExpenseApproverIdList = newDefaultValue.ExpenseApproverIdList;
                    existingDefaultValue.UpdatedBy = newDefaultValue.UpdatedBy;
                    existingDefaultValue.UpdatedDate = newDefaultValue.UpdatedDate;
                    existingDefaultValue.IsActive = newDefaultValue.IsActive;
                    existingDefaultValue.HierarchyIdList = string.Join(",", leafNode.ParentHierarchyList.Select(t => t.Id).ToList().GetStringListFromIntegerList());
                    existingDefaultValue.DisplayText = leafNode.HierarchyDisplayText;
                }
                else
                {
                    HeadDefaultValue toAdd = new HeadDefaultValue();
                    toAdd.Amount = newDefaultValue.Amount;
                    toAdd.BankName = newDefaultValue.BankName;
                    toAdd.CreatedBy = newDefaultValue.CreatedBy;
                    toAdd.CreatedDate = newDefaultValue.CreatedDate;
                    toAdd.ExpenseApproverIdList = newDefaultValue.ExpenseApproverIdList;
                    toAdd.IsActive = newDefaultValue.IsActive;
                    toAdd.IsAutoApproved = newDefaultValue.IsAutoApproved;
                    toAdd.Narration = newDefaultValue.Narration;
                    toAdd.TransactionModeValue = newDefaultValue.TransactionModeValue;
                    toAdd.UpdatedBy = newDefaultValue.UpdatedBy;
                    toAdd.UpdatedDate = newDefaultValue.UpdatedDate;

                    toAdd.HierarchyIdList = string.Join(",", leafNode.ParentHierarchyList.Select(t => t.Id).ToList().GetStringListFromIntegerList());
                    toAdd.DisplayText = leafNode.HierarchyDisplayText;
                    toAdd.TransactionTypeId = leafNode.Id;
                    context.HeadDefaultValues.AddObject(toAdd);
                }
            }

            context.SaveChanges();
            SettingsAndStaticListLoader.LoadHeadDefaultValues();
        }

        public List<BankAccount> SetBankBalancesOriginal(List<BankAccount> bankAccountList, AlfaRomeoEntities context)
        {
            foreach (BankAccount bankAccount in bankAccountList)
            {
                var allBankTransactions = context.TransactionMasters.Where(t => t.IsActive &&
                                                                                t.BankAccountsId.HasValue &&
                                                                                t.BankAccountsId.Value.Equals(bankAccount.Id));
                decimal totalExpense = 0;
                decimal totalIncome = 0;
                if (allBankTransactions.Count() > 0)
                {
                    var expenseTransactions = allBankTransactions.Where(t => t.Head.Equals("head_expense"));
                    if (expenseTransactions.Count() > 0)
                    {
                        totalExpense = expenseTransactions.Sum(r => r.TransactionAmount);
                    }

                    var incomeTransactions = allBankTransactions.Where(t => t.Head.Equals("head_income"));
                    if (incomeTransactions.Count() > 0)
                    {
                        totalIncome = incomeTransactions.Sum(r => r.TransactionAmount);
                    }
                }

                bankAccount.AvailableBalance = bankAccount.FinancialYearOpeningBalance + totalIncome - totalExpense;

                
                var allReconciledBankTransactions = context.TransactionMasters.Where(t => t.IsActive &&
                                                                                t.BankDate.HasValue &&
                                                                                t.BankAccountsId.HasValue &&
                                                                                t.BankAccountsId.Value.Equals(bankAccount.Id));
                if (allBankTransactions.Count() > 0)
                {
                    decimal totalReconciledExpense = 0;
                    var totalReconciledExpenseTransactions = allReconciledBankTransactions.Where(t => t.Head.Equals("head_expense"));
                    if (totalReconciledExpenseTransactions.Count() > 0)
                    {
                        totalReconciledExpense = totalReconciledExpenseTransactions.Sum(r => r.TransactionAmount);
                    }

                    decimal totalReconciledIncome = 0;
                    var totalReconciledIncomeTransactions = allReconciledBankTransactions.Where(t => t.Head.Equals("head_income"));
                    if (totalReconciledIncomeTransactions.Count() > 0)
                    {
                        totalReconciledIncome = totalReconciledIncomeTransactions.Sum(r => r.TransactionAmount);
                    }

                    bankAccount.ReonciledBalance = bankAccount.FinancialYearOpeningBalance + totalReconciledIncome - totalReconciledExpense;
                }
                else
                {
                    bankAccount.ReonciledBalance = 0;
                }

                // set the earliest and latest bank dates
                bankAccount.LatestBankDateTimeInCurrentFY = allReconciledBankTransactions.Max(t => t.BankDate.Value);
                bankAccount.EarliestBankDateTimeInCurrentFY = allReconciledBankTransactions.Min(t => t.BankDate.Value);

            }

            return bankAccountList;
        }

        public List<BankAccount> SetBankBalances(List<BankAccount> bankAccountList, AlfaRomeoEntities context)
        {
            foreach (BankAccount bankAccount in bankAccountList)
            {
                var allBankTransactions = context.TransactionMasters.Where(t => t.IsActive &&
                                                                                t.BankAccountsId.HasValue &&
                                                                                t.BankAccountsId.Value.Equals(bankAccount.Id));
                decimal totalExpense = 0;
                decimal totalIncome = 0;
                if (allBankTransactions.Count() > 0)
                {
                    var expenseTransactions = allBankTransactions.Where(t => t.Head.Equals("head_expense"));
                    if (expenseTransactions.Count() > 0)
                    {
                        totalExpense = expenseTransactions.Sum(r => r.TransactionAmount);
                    }

                    var incomeTransactions = allBankTransactions.Where(t => t.Head.Equals("head_income") && t.BankDate.HasValue);
                    if (incomeTransactions.Count() > 0)
                    {
                        totalIncome = incomeTransactions.Sum(r => r.TransactionAmount);
                    }
                }

                bankAccount.AvailableBalance = bankAccount.FinancialYearOpeningBalance + totalIncome - totalExpense;


                var allReconciledBankTransactions = context.TransactionMasters.Where(t => t.IsActive &&
                                                                                t.BankDate.HasValue &&
                                                                                t.BankAccountsId.HasValue &&
                                                                                t.BankAccountsId.Value.Equals(bankAccount.Id));
                if (allBankTransactions.Count() > 0)
                {
                    decimal totalReconciledExpense = 0;
                    var totalReconciledExpenseTransactions = allReconciledBankTransactions.Where(t => t.Head.Equals("head_expense"));
                    if (totalReconciledExpenseTransactions.Count() > 0)
                    {
                        totalReconciledExpense = totalReconciledExpenseTransactions.Sum(r => r.TransactionAmount);
                    }

                    decimal totalReconciledIncome = 0;
                    var totalReconciledIncomeTransactions = allReconciledBankTransactions.Where(t => t.Head.Equals("head_income"));
                    if (totalReconciledIncomeTransactions.Count() > 0)
                    {
                        totalReconciledIncome = totalReconciledIncomeTransactions.Sum(r => r.TransactionAmount);
                    }

                    bankAccount.ReonciledBalance = bankAccount.FinancialYearOpeningBalance + totalReconciledIncome - totalReconciledExpense;
                }
                else
                {
                    bankAccount.ReonciledBalance = 0;
                }

                // set the earliest and latest bank dates
                bankAccount.LatestBankDateTimeInCurrentFY = allReconciledBankTransactions.Max(t => t.BankDate.Value);
                bankAccount.EarliestBankDateTimeInCurrentFY = allReconciledBankTransactions.Min(t => t.BankDate.Value);

            }

            return bankAccountList;
        }

        public List<TransactionMaster> GetReconciledBankRecords(int bankAccountsId, int? pageNumber, int? numberOfRecords, out int totalNoOfRecords, AlfaRomeoEntities context)
        {
            if (!pageNumber.HasValue && !numberOfRecords.HasValue)
            {
                throw new DALException("Please provide both the page number and the number of records to fetch.");
            }

            if (pageNumber.HasValue != numberOfRecords.HasValue)
            {
                throw new DALException("Please provide both the page number and the number of records to fetch.");
            }

            var allReconciledBankTransactions = context.TransactionMasters.Where(t => t.IsActive &&
                                                                                t.BankDate.HasValue &&
                                                                                t.BankAccountsId.HasValue &&
                                                                                t.BankAccountsId.Value.Equals(bankAccountsId));

            totalNoOfRecords = allReconciledBankTransactions.Count();

            return allReconciledBankTransactions.OrderByDescending(t => t.BankDate.Value).Skip(pageNumber.Value * numberOfRecords.Value).Take(numberOfRecords.Value).ToList();
        }

        public List<TransactionMaster> SetRollOverAmount(List<TransactionMaster> transactionMasterList,
                                                        int pettyCashIdForRollOverAmount, AlfaRomeoEntities context)
        {
            foreach (TransactionMaster cashExpenseTransactionMaster in transactionMasterList)
            {
                decimal usedUpAmount = context.PettyCashUsages.Where(t => t.IsActive &&
                                                                        t.PettyCashId.Equals(pettyCashIdForRollOverAmount) &&
                                                                        t.TransactionMasterId.Equals(cashExpenseTransactionMaster.Id)).Sum(r => r.UsedUpAmount);
                cashExpenseTransactionMaster.RollOverAmount = cashExpenseTransactionMaster.TransactionAmount - usedUpAmount;
            }

            return transactionMasterList;
        }

        public List<TransactionMaster> SetCurrentBalance(List<TransactionMaster> transactionMasterList, bool isBankDateSelected,
                                                         int bankAccountsId,AlfaRomeoEntities context)
        {
            if (transactionMasterList.Count == 0)
            {
                return transactionMasterList;
            }

            BankAccount requiredBankAccount = context.BankAccounts.Where(t => t.IsActive && t.Id.Equals(bankAccountsId)).FirstOrDefault();
            if (requiredBankAccount == null)
            {
                return transactionMasterList;
            }

            decimal openingBalance = requiredBankAccount.FinancialYearOpeningBalance;

            DateTime maxDateTime;
            if (isBankDateSelected)
            {
                maxDateTime = transactionMasterList.Max(t => t.BankDate.Value);
            }
            else
            {
                maxDateTime = transactionMasterList.Max(t => t.TransactionDate);
            }

            List<TransactionMaster> requiredTransactionMasterList = new List<TransactionMaster>();
            if (isBankDateSelected)
            {
                requiredTransactionMasterList.AddRange(context.TransactionMasters.Where(t => t.IsActive && t.BankDate.HasValue &&
                                                      t.BankAccountsId.HasValue &&
                                                      t.BankAccountsId.Value.Equals(bankAccountsId) && 
                                                      t.BankDate.Value.CompareTo(maxDateTime) <= 0).OrderBy(m => m.BankDate.Value).ToList());
            }
            else
            {
                requiredTransactionMasterList.AddRange(context.TransactionMasters.Where(t => t.IsActive &&
                                                      t.TransactionDate.CompareTo(maxDateTime) <= 0).OrderBy(m => m.TransactionDate).ToList());
            }

            decimal currentBalance = openingBalance;
            foreach (TransactionMaster transactionMaster in requiredTransactionMasterList)
            {
                if (transactionMaster.Head.Equals("head_income"))
                {
                    transactionMaster.CurrentBalance = currentBalance + transactionMaster.TransactionAmount;
                }
                else
                {
                    transactionMaster.CurrentBalance = currentBalance - transactionMaster.TransactionAmount;
                }

                currentBalance = transactionMaster.CurrentBalance;
            }


            for (int index = 0; index < requiredTransactionMasterList.Count; index++)
            {
                TransactionMaster transactionMasterWithCurrentBalanceSet = requiredTransactionMasterList[index];

                TransactionMaster transactionMasterInInput = transactionMasterList.Where(t => t.Id.Equals(transactionMasterWithCurrentBalanceSet.Id)).FirstOrDefault();

                if (transactionMasterInInput == null)
                {
                    requiredTransactionMasterList.RemoveAt(index);
                    index--;
                }
            }

            if (isBankDateSelected)
            {
                requiredTransactionMasterList = requiredTransactionMasterList.OrderByDescending(m => m.BankDate.Value).ToList();
            }
            else
            {
                requiredTransactionMasterList = requiredTransactionMasterList.OrderByDescending(m => m.TransactionDate).ToList();
            }

            return requiredTransactionMasterList;
        }

        //public decimal GetCurrentBalance(TransactionMaster transactionMaster, bool isBankDateSelected,
        //                                                                          AlfaRomeoEntities context)
        //{
        //    decimal openingBalance = GetIncomeExpenseSummary(Utils.FinancialYear, context).FinancialYearOpeningBalanceFromAllBanks;
        //    decimal incomeTotal = 0;
        //    decimal expenseTotal = 0;

        //    var incomeTotalRecords = context.TransactionMasters.Where(t => t.IsActive);
        //    if (isBankDateSelected)
        //    {
        //        incomeTotalRecords = incomeTotalRecords.Where(t => t.BankDate.HasValue).OrderBy(r => r.BankDate.Value);
        //        incomeTotalRecords = incomeTotalRecords.Where(r => (r.BankDate.Value.CompareTo(transactionMaster.BankDate.Value) <= 0) &&
        //                                                   r.Head.Equals("head_income") &&
        //                                                   r.Id != transactionMaster.Id);
        //    }
        //    else
        //    {
        //        incomeTotalRecords = incomeTotalRecords.OrderBy(t => t.TransactionDate).
        //                                        Where(r => (r.TransactionDate.CompareTo(transactionMaster.TransactionDate) <= 0) &&
        //                                                   r.Head.Equals("head_income") &&
        //                                                   r.Id != transactionMaster.Id);
        //    }
        //    if(incomeTotalRecords.Count() > 0)
        //    {
        //        incomeTotal = incomeTotalRecords.Sum(s => s.TransactionAmount);
        //    }

        //    var expenseTotalRecords = context.TransactionMasters.Where(t => t.IsActive);
        //    if (isBankDateSelected)
        //    {
        //        expenseTotalRecords = expenseTotalRecords.Where(t => t.BankDate.HasValue).OrderBy(r => r.BankDate.Value);
        //        expenseTotalRecords = expenseTotalRecords.Where(r => (r.BankDate.Value.CompareTo(transactionMaster.BankDate.Value) <= 0) &&
        //                                                   r.Head.Equals("head_expense") &&
        //                                                   r.Id != transactionMaster.Id);
        //    }
        //    else
        //    {
        //        expenseTotalRecords = context.TransactionMasters.OrderBy(t => t.TransactionDate).
        //                                        Where(r => (r.TransactionDate.CompareTo(transactionMaster.TransactionDate) <= 0) &&
        //                                                    r.Head.Equals("head_expense") &&
        //                                                    r.Id != transactionMaster.Id);
        //    }

        //    if (expenseTotalRecords.Count() > 0)
        //    {
        //        expenseTotal = expenseTotalRecords.Sum(s => s.TransactionAmount);
        //    }

        //    decimal currentBalance = openingBalance + incomeTotal - expenseTotal;
        //    if (transactionMaster.Head.Equals("head_expense"))
        //    {
        //        currentBalance = currentBalance - transactionMaster.TransactionAmount;
        //    }
        //    else
        //    {
        //        currentBalance = currentBalance + transactionMaster.TransactionAmount;
        //    }

        //    return currentBalance;
                                                                              
        //}

        public List<TransactionMaster> GetTransactionMasterListAll(int pageNumber, int numberOfRows, out int totalRows,
                                                                  AlfaRomeoEntities context)
        {
            totalRows = context.TransactionMasters.Include("TransactionMasterChequeDetails")
                                                .Where(t => t.IsActive).Count();

            List<TransactionMaster> transactionMasterList = context.TransactionMasters.Include("TransactionMasterChequeDetails")
                                                .Where(t => t.IsActive).OrderByDescending(r => r.TransactionDate).
                                                 Skip(pageNumber * numberOfRows).Take(numberOfRows).ToList();

            RemoveDeletedChildEntriesFromTransactionMaster(transactionMasterList);

            return transactionMasterList;
        }

        public List<TransactionMaster> GetUnreconciledTransactionMasterList(bool isAscending, AlfaRomeoEntities context)
        {
            var idsToExclude = context.TransactionMasters.Where(t => t.IsActive && t.BankDate == null &&
                                                                         t.Head.Equals("head_expense") &&
                                                                         t.TransactionModeValue.Equals("transaction_mode_cash")).Select(r => r.Id);
            
            if (isAscending)
            {
                
                return context.TransactionMasters.Where(t => t.IsActive && t.BankDate == null &&
                                                                  !idsToExclude.Contains(t.Id)).
                                                OrderBy(r => r.TransactionDate).Take(25).ToList();

            }
            else
            {
                return context.TransactionMasters.Where(t => t.IsActive && t.BankDate == null &&
                                                                  !idsToExclude.Contains(t.Id)).
                                                OrderByDescending(r => r.TransactionDate).Take(25).ToList();
            }
        }

        public List<TransactionMaster> GetFilteredTransactionMasterListOriginal(FilterCriteria filterCriteria, int? pageNumber, int? numberOfRecords,
                                                                                    out int totalNumberOfRecords, AlfaRomeoEntities context)
        {
            totalNumberOfRecords = 0;
            return null;
            //if (pageNumber.HasValue != numberOfRecords.HasValue)
            //{
            //    throw new DALException("Please provide both the page number and the number of records to fetch. Or, provide none i.e. pass null for both");
            //}

            //var result = context.TransactionMasters.Where(t => t.IsActive);

            //if (filterCriteria.DateFrom.HasValue)
            //{
            //    if (filterCriteria.TransactionDateSelected)
            //    {
            //        result = result.Where(t => t.TransactionDate.CompareTo(filterCriteria.DateFrom.Value) >= 0 &&
            //                                   t.TransactionDate.CompareTo(filterCriteria.DateTo.Value) <= 0);
            //    }
            //    else
            //    {
            //        result = result.Where(t => t.BankDate.HasValue && t.BankAccountsId.HasValue &&
            //                                   t.BankAccountsId.Value.Equals(filterCriteria.BankAccountsId.Value) &&
            //                                   t.BankDate.Value.CompareTo(filterCriteria.DateFrom.Value) >= 0 &&
            //                                   t.BankDate.Value.CompareTo(filterCriteria.DateTo.Value) <= 0);
            //    }
            //}

            //if (filterCriteria.AmountFrom.HasValue)
            //{
            //    result = result.Where(t => t.TransactionAmount >= filterCriteria.AmountFrom.Value &&
            //                                t.TransactionAmount <= filterCriteria.AmountTo.Value);
            //}

            //if (filterCriteria.FirstHeadDetailsId.HasValue)
            //{
            //    string firstHead = "[" + filterCriteria.FirstHeadDetailsId.Value.ToString() + "]";
            //    if (filterCriteria.SecondHeadDetailsId.HasValue && filterCriteria.ThirdHeadDetailsId.HasValue)
            //    {
            //        string secondHead = "[" + filterCriteria.SecondHeadDetailsId.Value.ToString() + "]";
            //        string thirdHead = "[" + filterCriteria.ThirdHeadDetailsId.Value.ToString() + "]";

            //        result = result.Where(t => t.HeadDetailsHierarchyIdList.Contains(firstHead) ||
            //                                   t.HeadDetailsHierarchyIdList.Contains(secondHead) ||
            //                                   t.HeadDetailsHierarchyIdList.Contains(thirdHead));
            //    }
            //    else if (filterCriteria.SecondHeadDetailsId.HasValue)
            //    {
            //        string secondHead = "[" + filterCriteria.SecondHeadDetailsId.Value.ToString() + "]";
            //        result = result.Where(t => t.HeadDetailsHierarchyIdList.Contains(firstHead) ||
            //                                  t.HeadDetailsHierarchyIdList.Contains(secondHead));
            //    }
            //    else
            //    {
            //        result = result.Where(t => t.HeadDetailsHierarchyIdList.Contains(firstHead));
            //    }
            //}

            //if (!string.IsNullOrEmpty(filterCriteria.Narration))
            //{
            //    string narrationLowerCase = filterCriteria.Narration.ToLower();
            //    result = result.Where(t => t.Narration.ToLower().Contains(narrationLowerCase));
            //}

            //if (filterCriteria.ChequeNumberFrom.HasValue)
            //{
            //    result = result.Where(t => t.TransactionMasterChequeDetails.Any(r => r.ChequeNumber >= filterCriteria.ChequeNumberFrom && r.ChequeNumber <= filterCriteria.ChequeNumberTo));
            //}

            //if (filterCriteria.TransactionDateSelected)
            //{
            //    if (!pageNumber.HasValue)
            //    {
            //        totalNumberOfRecords = result.Count();
            //        return result.OrderByDescending(t => t.TransactionDate).ToList();
            //    }
            //    else
            //    {
            //        totalNumberOfRecords = result.Count();
            //        return result.OrderByDescending(t => t.TransactionDate).Skip(pageNumber.Value * numberOfRecords.Value).Take(numberOfRecords.Value).ToList();
            //    }
            //}
            //else
            //{
            //    if (!pageNumber.HasValue)
            //    {
            //        totalNumberOfRecords = result.Count();
            //        return result.OrderByDescending(t => t.BankDate.Value).ToList();
            //    }
            //    else
            //    {
            //        totalNumberOfRecords = result.Count();
            //        return result.OrderByDescending(t => t.BankDate.Value).Skip(pageNumber.Value * numberOfRecords.Value).Take(numberOfRecords.Value).ToList();
            //    }
            //}
        }
        
        // started modifying this for the new reconciliation tab, new user control etc.
        // on 10 july 2013
        public List<TransactionMaster> GetFilteredTransactionMasterList(FilterCriteria filterCriteria, int? pageNumber, int? numberOfRecords, 
                                                                                    out int totalNumberOfRecords, AlfaRomeoEntities context)
        {
            if (pageNumber.HasValue != numberOfRecords.HasValue)
            {
                throw new DALException("Please provide both the page number and the number of records to fetch. Or, provide none i.e. pass null for both");
            }

            var result = context.TransactionMasters.Select(t => t);

            if (filterCriteria.IncludeDeletedTransactions.HasValue)
            {
                if (!filterCriteria.IncludeDeletedTransactions.Value)
                {
                    result = result.Where(t => !t.IsActive);
                }
            }
            else
            {
                result = result.Where(t => t.IsActive);
            }

            if (filterCriteria.ReconciliationMode)
            {
                result = result.Where(t => !(t.Head + t.TransactionModeValue).Equals("head_expensetransaction_mode_cash"));
            }

            if (filterCriteria.TransactionMasterIdList.Count > 0)
            {
                result = result.Where(t => filterCriteria.TransactionMasterIdList.Contains(t.Id));
            }

            if (filterCriteria.DateFrom.HasValue)
            {
                if (filterCriteria.TransactionDateSelected)
                {
                    result = result.Where(t => t.TransactionDate.CompareTo(filterCriteria.DateFrom.Value) >= 0 &&
                                               t.TransactionDate.CompareTo(filterCriteria.DateTo.Value) <= 0);
                }
                else
                {
                    result = result.Where(t => t.BankDate.HasValue && 
                                               t.BankDate.Value.CompareTo(filterCriteria.DateFrom.Value) >= 0 &&
                                               t.BankDate.Value.CompareTo(filterCriteria.DateTo.Value) <= 0);
                }
            }

            if (filterCriteria.OnlyReconciledRecords != filterCriteria.OnlyUnreconciledRecords)
            {
                if (filterCriteria.OnlyReconciledRecords)
                {
                    result = result.Where(t => t.BankDate.HasValue);
                }
                else
                {
                    result = result.Where(t => !t.BankDate.HasValue);
                }
            }

            if (filterCriteria.OnlyIncomeRecords != filterCriteria.OnlyExpenseRecords)
            {
                if (filterCriteria.OnlyExpenseRecords)
                {
                    result = result.Where(t => t.Head.Equals("head_expense"));
                }
                else
                {
                    result = result.Where(t => t.Head.Equals("head_income"));
                }
            }

            

            if (filterCriteria.BankAccountsIdList.Count > 0)
            {
                result = result.Where(t => t.BankAccountsId.HasValue &&
                                               filterCriteria.BankAccountsIdList.Contains(t.BankAccountsId.Value));
            }

            if (filterCriteria.AmountFrom.HasValue)
            {
                result = result.Where(t => t.TransactionAmount >= filterCriteria.AmountFrom.Value &&
                                            t.TransactionAmount <= filterCriteria.AmountTo.Value); 
            }

            if (filterCriteria.FirstHeadDetailsId.HasValue)
            {
                string firstHead = "[" + filterCriteria.FirstHeadDetailsId.Value.ToString() + "]";
                if (filterCriteria.SecondHeadDetailsId.HasValue && filterCriteria.ThirdHeadDetailsId.HasValue)
                {
                    string secondHead = "[" + filterCriteria.SecondHeadDetailsId.Value.ToString() + "]";
                    string thirdHead = "[" + filterCriteria.ThirdHeadDetailsId.Value.ToString() + "]";

                    result = result.Where(t => t.HeadDetailsHierarchyIdList.Contains(firstHead) ||
                                               t.HeadDetailsHierarchyIdList.Contains(secondHead) ||
                                               t.HeadDetailsHierarchyIdList.Contains(thirdHead));
                }
                else if (filterCriteria.SecondHeadDetailsId.HasValue)
                {
                    string secondHead = "[" + filterCriteria.SecondHeadDetailsId.Value.ToString() + "]";
                    result = result.Where(t => t.HeadDetailsHierarchyIdList.Contains(firstHead) ||
                                              t.HeadDetailsHierarchyIdList.Contains(secondHead));
                }
                else
                {
                    result = result.Where(t => t.HeadDetailsHierarchyIdList.Contains(firstHead));
                }
            }

            if (filterCriteria.TransactionModeStringList.Count > 0)
            {
                result = result.Where(t => filterCriteria.TransactionModeStringList.Contains(t.TransactionModeValue));
            }

            if (filterCriteria.ExpenseApproverIdSquareBracketedList.Count > 0)
            {
                result = result.Where(t => filterCriteria.ExpenseApproverIdSquareBracketedList.Any(r => !string.IsNullOrEmpty(t.ExpenseApproverIdList) && t.ExpenseApproverIdList.Contains(r)));
            }

            if (filterCriteria.BankAccountsIdList.Count > 0)
            {
                result = result.Where(t => t.BankAccountsId.HasValue && filterCriteria.BankAccountsIdList.Contains(t.BankAccountsId.Value));
            }

            if (!string.IsNullOrEmpty(filterCriteria.Narration))
            {
                string narrationLowerCase = filterCriteria.Narration.ToLower();
                result = result.Where(t => t.Narration.ToLower().Contains(narrationLowerCase));
            }
            

            if (filterCriteria.ChequeNumberFrom.HasValue)
            {
                result = result.Where(t => t.TransactionMasterChequeDetails.Any(r => r.ChequeNumber >= filterCriteria.ChequeNumberFrom && r.ChequeNumber <= filterCriteria.ChequeNumberTo));
            }

            if (filterCriteria.IsVoucherSelected.HasValue)
            {
                if (filterCriteria.IsVoucherSelected.Value)
                {
                    result = result.Where(t => t.Head.Equals("head_expense") &&
                                            t.VoucherReceiptNumber >= filterCriteria.VoucherReceiptFrom &&
                                            t.VoucherReceiptNumber <= filterCriteria.VoucherReceiptTo);
                }
                else
                {
                    result = result.Where(t => t.Head.Equals("head_income") &&
                                            t.VoucherReceiptNumber >= filterCriteria.VoucherReceiptFrom &&
                                            t.VoucherReceiptNumber <= filterCriteria.VoucherReceiptTo);
                }
            }

            if (filterCriteria.TransactionDateSelected)
            {
                if (!pageNumber.HasValue)
                {
                    totalNumberOfRecords = result.Count();
                    return result.OrderByDescending(t => t.TransactionDate).ToList();
                }
                else
                {
                    totalNumberOfRecords = result.Count();
                    return result.OrderByDescending(t => t.TransactionDate).Skip(pageNumber.Value * numberOfRecords.Value).Take(numberOfRecords.Value).ToList();
                }
            }
            else
            {
                if (!pageNumber.HasValue)
                {
                    totalNumberOfRecords = result.Count();
                    return result.OrderByDescending(t => t.BankDate.Value).ToList();
                }
                else
                {
                    totalNumberOfRecords = result.Count();
                    return result.OrderByDescending(t => t.BankDate.Value).Skip(pageNumber.Value * numberOfRecords.Value).Take(numberOfRecords.Value).ToList();
                }
            }
        }

        public List<TransactionMaster> GetTransactionMasterListNOTUSED(DateTime? transactionDateFrom, 
                                                                DateTime? transactionDateTo,
                                                                int? amountFrom,
                                                                int? amountTo,
                                                                string containsTheseTransactionTypes,
                                                                string narration,
                                                                AlfaRomeoEntities context)
        {
            
            List<TransactionMaster> queryResult = new List<TransactionMaster>();

            // Get Id list based on transaction types
            List<int> transactionMasterIdList = new List<int>();
            if (!string.IsNullOrEmpty(containsTheseTransactionTypes))
            {
                //string[] idList = containsTheseTransactionTypes.Split(new string[] { "," },  StringSplitOptions.RemoveEmptyEntries);
                
                //List<int> transTypeIdList = new List<int>();
                //foreach (string id in idList)
                //{
                //    transTypeIdList.Add(int.Parse(id));
                //}

                transactionMasterIdList.AddRange(context.TransactionMasters.Where(t => t.HeadDetailsHierarchyIdList.Contains(containsTheseTransactionTypes)).Select(r => r.Id).Distinct());

                //transactionMasterIdList.AddRange(context.TransactionMasterTypeDetails.Where(t => containsOneOfTheTransactionTypes.Contains(t.GroupTextLong)).Select(r => r.TransactionMasterId).Distinct());
                
            }

            // when both the date ranges  and the amount ranges are given
            if (transactionDateFrom != null && transactionDateTo != null && 
                amountFrom != null && amountTo != null)
            {
                if (transactionMasterIdList.Count == 0)
                {
                    if (!string.IsNullOrEmpty(narration))
                    {
                        queryResult.AddRange(context.TransactionMasters.Where(t => t.IsActive && t.TransactionDate >= transactionDateFrom.Value &&
                                                              t.TransactionDate <= transactionDateTo.Value &&
                                                              t.TransactionAmount >= amountFrom.Value &&
                                                              t.TransactionAmount <= amountTo.Value &&
                                                              t.Narration.ToLower().Trim().Contains(narration.ToLower().Trim())).ToList());
                    }
                    else
                    {
                        queryResult.AddRange(context.TransactionMasters.Where(t => t.IsActive && t.TransactionDate >= transactionDateFrom.Value &&
                                                              t.TransactionDate <= transactionDateTo.Value &&
                                                              t.TransactionAmount >= amountFrom.Value &&
                                                              t.TransactionAmount <= amountTo.Value).ToList());
                    }

                }
                else
                {
                    if (!string.IsNullOrEmpty(narration))
                    {
                        queryResult.AddRange(context.TransactionMasters.Where(t => t.IsActive && transactionMasterIdList.Contains(t.Id) &&
                                                              t.TransactionDate >= transactionDateFrom.Value &&
                                                              t.TransactionDate <= transactionDateTo.Value &&
                                                              t.TransactionAmount >= amountFrom.Value &&
                                                              t.TransactionAmount <= amountTo.Value &&
                                                              t.Narration.ToLower().Trim().Contains(narration.ToLower().Trim())).ToList());
                    }
                    else
                    {
                        queryResult.AddRange(context.TransactionMasters.Where(t => t.IsActive && transactionMasterIdList.Contains(t.Id) &&
                                                              t.TransactionDate >= transactionDateFrom.Value &&
                                                              t.TransactionDate <= transactionDateTo.Value &&
                                                              t.TransactionAmount >= amountFrom.Value &&
                                                              t.TransactionAmount <= amountTo.Value).ToList());
                    }

                }
            }

            // when the date ranges are given and the amounts are not given
            if (transactionDateFrom != null && transactionDateTo != null &&
                (amountFrom == null || amountTo == null))
            {
                if (transactionMasterIdList.Count == 0)
                {
                    if (!string.IsNullOrEmpty(narration))
                    {
                        queryResult.AddRange(context.TransactionMasters.Where(t => t.IsActive && t.TransactionDate >= transactionDateFrom.Value &&
                                                              t.TransactionDate <= transactionDateTo.Value &&
                                                              t.Narration.ToLower().Trim().Contains(narration.ToLower().Trim())).ToList());
                    }
                    else
                    {
                        queryResult.AddRange(context.TransactionMasters.Where(t => t.IsActive && t.TransactionDate >= transactionDateFrom.Value &&
                                                              t.TransactionDate <= transactionDateTo.Value).ToList());
                    }

                }
                else
                {
                    if (!string.IsNullOrEmpty(narration))
                    {
                        queryResult.AddRange(context.TransactionMasters.Where(t => t.IsActive && transactionMasterIdList.Contains(t.Id) &&
                                                              t.TransactionDate >= transactionDateFrom.Value &&
                                                              t.TransactionDate <= transactionDateTo.Value &&
                                                              t.Narration.ToLower().Trim().Contains(narration.ToLower().Trim())).ToList());
                    }
                    else
                    {
                        queryResult.AddRange(context.TransactionMasters.Where(t => t.IsActive && transactionMasterIdList.Contains(t.Id) &&
                                                              t.TransactionDate >= transactionDateFrom.Value &&
                                                              t.TransactionDate <= transactionDateTo.Value).ToList());
                    }

                }
            }

            // when the amount ranges are given but the dates are not given
            if (amountFrom != null && amountTo != null &&
                (transactionDateFrom == null || transactionDateTo == null))
            {
                if (transactionMasterIdList.Count == 0)
                {
                    if (!string.IsNullOrEmpty(narration))
                    {
                        queryResult.AddRange(context.TransactionMasters.Where(t => t.IsActive && t.TransactionAmount >= amountFrom.Value &&
                                                              t.TransactionAmount <= amountTo.Value &&
                                                              t.Narration.ToLower().Trim().Contains(narration.ToLower().Trim())).ToList());
                    }
                    else
                    {
                        queryResult.AddRange(context.TransactionMasters.Where(t => t.IsActive && t.TransactionAmount >= amountFrom.Value &&
                                                              t.TransactionAmount <= amountTo.Value).ToList());
                    }

                }
                else
                {
                    if (!string.IsNullOrEmpty(narration))
                    {
                        queryResult.AddRange(context.TransactionMasters.Where(t => t.IsActive && transactionMasterIdList.Contains(t.Id) &&
                                                              t.TransactionAmount >= amountFrom.Value &&
                                                              t.TransactionAmount <= amountTo.Value &&
                                                              t.Narration.ToLower().Trim().Contains(narration.ToLower().Trim())).ToList());
                    }
                    else
                    {
                        queryResult.AddRange(context.TransactionMasters.Where(t => t.IsActive && transactionMasterIdList.Contains(t.Id) &&
                                                              t.TransactionAmount >= amountFrom.Value &&
                                                              t.TransactionAmount <= amountTo.Value).ToList());
                    }

                }
            }

            
            // when only the narration is given
            if ((amountFrom == null || amountTo == null) && (transactionDateFrom == null || transactionDateTo == null) && !string.IsNullOrEmpty(narration))
            {
                if (transactionMasterIdList.Count == 0)
                {
                    queryResult.AddRange(context.TransactionMasters.Where(t => t.IsActive &&  
                                                              t.Narration.ToLower().Trim().Contains(narration.ToLower().Trim())).ToList());
                }
                else
                {
                    queryResult.AddRange(context.TransactionMasters.Where(t => t.IsActive && transactionMasterIdList.Contains(t.Id) &&
                                                              t.Narration.ToLower().Trim().Contains(narration.ToLower().Trim())).ToList());
                }
            }

            RemoveDeletedChildEntriesFromTransactionMaster(queryResult);
            return queryResult;
        }

        public int GetPettyCashEntrycount(AlfaRomeoEntities context)
        {
            return
                context.PettyCashes.Where(r => r.IsActive).Count();
            
        }

        public List<PettyCash> GetPettyCashList(int pageNumber, int numberOfRecords, AlfaRomeoEntities context)
        {
            List<PettyCash> pagedPettyCashList = context.PettyCashes.Where(r => r.IsActive).OrderByDescending(t => t.CreatedDate).Skip(pageNumber).Take(numberOfRecords).ToList();
            return pagedPettyCashList;
        }

        public decimal GetAvailablePettyCash(AlfaRomeoEntities context)
        {
            var pettyCashWithEntriesWithNonZeroBalance = context.PettyCashes.Where(t => t.IsActive && t.BalanceAmount > 0);
            if (pettyCashWithEntriesWithNonZeroBalance.Count() > 0)
            {
                return pettyCashWithEntriesWithNonZeroBalance.Sum(r => r.BalanceAmount);
            }
            else
            {
                return 0;
            }
        }

        public List<int> GetTransactionMasterIdListByPettyCashIdList(List<int> pettyCashIdList, AlfaRomeoEntities context)
        {
            List<int> requiredIdList = new List<int>();
            foreach(int pettyCashId in pettyCashIdList)
            {
                string pettyCashIdString = pettyCashId.ToString();
                var temp = context.TransactionMasters.Where(
                                            t => t.PettyCashIdList.Contains(pettyCashIdString)).Select(r => r.Id);

                if (temp.Count() > 0)
                {
                    requiredIdList.AddRange(temp.ToList());
                }
            }

            return requiredIdList;
        }

        public List<ExpenseApprover> GetExpenseApproverList(AlfaRomeoEntities context)
        {
            return context.ExpenseApprovers.Where(t => t.IsActive).ToList();
        }

        public List<ExpenseApprover> GetExpenseApproverListByIdList(List<int> idList, AlfaRomeoEntities context)
        {
            return context.ExpenseApprovers.Where(t => t.IsActive && idList.Contains(t.Id)).ToList();
        }

        public List<string> GetExpenseApproverNameListByIdList(List<int> idList, AlfaRomeoEntities context)
        {
            return context.ExpenseApprovers.Where(t => t.IsActive && idList.Contains(t.Id)).Select(r => r.FirstName + " " + r.LastName).ToList();
        }

        public List<BankAccount> GetAllBankAccounts(FinancialStartEnd financialStartEnd,
                                                            AlfaRomeoEntities context)
        {
            return context.BankAccounts.Where(t => t.FinancialYearStart.Equals(financialStartEnd.FinancialYearStart) &&
                                                             t.FinancialYearEnd.Equals(financialStartEnd.FinancialYearEnd) &&
                                                             t.IsActive).ToList();
        }


        public BankAccount GetBankAccount(FinancialStartEnd financialStartEnd, 
                                                            string bankName,
                                                            AlfaRomeoEntities context)
        {
            return context.BankAccounts.Where(t => t.FinancialYearStart.Equals(financialStartEnd.FinancialYearStart) &&
                                                             t.FinancialYearEnd.Equals(financialStartEnd.FinancialYearEnd) &&
                                                             t.BankName.Equals(bankName) &&
                                                             t.IsActive).FirstOrDefault();
        }

        public SequenceNumber GetCurrentSequenceNumbers(AlfaRomeoEntities context, int financialYearStart, int financialYearEnd)
        {
            return context.SequenceNumbers.Where(t => t.FinancialYearStart.Equals(financialYearStart) && t.FinancialYearEnd.Equals(financialYearEnd)).FirstOrDefault();
        }

        public List<StaticList> GetListValuesByListType(string listType, AlfaRomeoEntities context)
        {
            return context.StaticLists.Where(t => t.ListType.ToLower().Equals(listType.ToLower()) && t.IsActive).Select(r => r).ToList();
        }

        public List<string> GetDistinctListTypes(AlfaRomeoEntities context)
        {
            return context.StaticLists.Select(t => t.ListType).Distinct().ToList();
        }

        public List<StaticList> GetListValuesByCode(List<string> listCodes, AlfaRomeoEntities context)
        {
            return context.StaticLists.Where(t => listCodes.Contains(t.ListItemCode) && t.IsActive).Select(r => r).ToList();
        }

        public List<PettyCashUsage> GetPettyCashUsage(int pettyCashId, AlfaRomeoEntities context)
        {
            return context.PettyCashUsages.Where(t => t.IsActive && t.PettyCashId.Equals(pettyCashId)).ToList();
        }

        // this will never be used now since there cannot be more than one cheque details entry per transaction
        // should be removed in due course.. when?? :D :D 
        public void SaveNewTransactionMasterChequeDetailList(List<TransactionMasterChequeDetail> transactionMasterChequeDetailListToSave,
                                                               string scannedImagesRootFolderPath,
                                                               AlfaRomeoEntities context)
        {
            Dictionary<string, byte[]> filesToSave = new Dictionary<string, byte[]>();
            foreach (TransactionMasterChequeDetail tansactionMasterChequeDetail in transactionMasterChequeDetailListToSave)
            {
                if (tansactionMasterChequeDetail.TransactionMasterId != 0)
                {
                    if (tansactionMasterChequeDetail.TransactionMasterReference == null)
                    {
                        tansactionMasterChequeDetail.TransactionMasterReference = new System.Data.Objects.DataClasses.EntityReference<TransactionMaster>();
                    }

                    if (tansactionMasterChequeDetail.TransactionMasterReference.EntityKey == null)
                    {
                        tansactionMasterChequeDetail.TransactionMasterReference.EntityKey = new System.Data.EntityKey("AlfaRomeoEntities.TransactionMasters", "Id", tansactionMasterChequeDetail.TransactionMasterId);
                    }
                }

                context.TransactionMasterChequeDetails.AddObject(tansactionMasterChequeDetail);
                if (tansactionMasterChequeDetail.FileContentChanged && !filesToSave.ContainsKey(tansactionMasterChequeDetail.ScannedChequeImagePath))
                {
                    filesToSave.Add(tansactionMasterChequeDetail.ScannedChequeImagePath, tansactionMasterChequeDetail.FileContent);
                }
            }

            // HISTORY - not needed
            context.SaveChanges();

            if (filesToSave.Count > 0)
            {
                Utils.CopyFiles(scannedImagesRootFolderPath, filesToSave);
            }
        }

        public void SavePettyCash(PettyCash pettyCash, AlfaRomeoEntities context)
        {
            //IncomeExpenseSummary incomeExpenseSummary  = this.GetIncomeExpenseSummary(Utils.FinancialYear, context);
            //incomeExpenseSummary.FinancialYearTotalIncome += pettyCash.Amount;
            if (pettyCash.Id == 0)
            {
                context.PettyCashes.AddObject(pettyCash);
            }
            context.SaveChanges();
        }

        public void UpdateBankAccountDetailsAndStatistics(AlfaRomeoEntities context)
        {
            // set LatestReconciledTransactionBankDate,  LastReconciliationActivityDate, NoOfUnreconciledChequeExpenseTransactions
            List<BankAccount> allBankAccounts = GetAllBankAccounts(Utils.FinancialYear, context);
            foreach (BankAccount reconciledBankAccount in allBankAccounts)
            {
                var bankWiseReconciledTransactions = context.TransactionMasters.Where(t => t.IsActive && t.BankAccountsId.HasValue &&
                                                                                           t.BankDate.HasValue &&
                                                                                           t.BankAccountsId.Value.Equals(reconciledBankAccount.Id));

                TransactionMaster latestReconciledRecord = bankWiseReconciledTransactions.OrderByDescending(t => t.BankDate.Value).FirstOrDefault();
                if (latestReconciledRecord != null)
                {
                    reconciledBankAccount.LatestReconciledTransactionBankDate = latestReconciledRecord.BankDate.Value;
                }
                else
                {
                    reconciledBankAccount.LatestReconciledTransactionBankDate = null;
                }

                TransactionMaster lastReconciledActivityRecord = bankWiseReconciledTransactions.OrderByDescending(t => t.UpdatedDate).FirstOrDefault();
                if (lastReconciledActivityRecord != null)
                {
                    reconciledBankAccount.LastReconciliationActivityDate = lastReconciledActivityRecord.UpdatedDate;
                }
                else
                {
                    reconciledBankAccount.LastReconciliationActivityDate = null;
                }


                int bankWiseUnreconciledExpenseTransactionsCount = context.TransactionMasters.Where(t => t.IsActive &&
                                                                                           !t.BankDate.HasValue &&
                                                                                           t.BankAccountsId.Value.Equals(reconciledBankAccount.Id) &&
                                                                                           t.Head.Equals("head_expense")).Count();

                reconciledBankAccount.NoOfUnreconciledExpenseTransactions = bankWiseUnreconciledExpenseTransactionsCount;
                
                int bankWiseUnreconciledIncomeTransactionsCount = context.TransactionMasters.Where(t => t.IsActive &&
                                                                                           !t.BankDate.HasValue &&
                                                                                           t.BankAccountsId.Value.Equals(reconciledBankAccount.Id) &&
                                                                                           t.Head.Equals("head_income")).Count();
                reconciledBankAccount.NoOfUnreconciledIncomeTransactions = bankWiseUnreconciledIncomeTransactionsCount;
            }

            context.SaveChanges();
            SettingsAndStaticListLoader.LoadBankAccounts();
        }

        public void SaveBankAccount(BankAccount bankAccount, AlfaRomeoEntities context)
        {
            if (bankAccount.Id == 0)
            {
                context.BankAccounts.AddObject(bankAccount);
            }
            context.SaveChanges();
        }


        public void ReconcileTransactions(List<TransactionMaster> transactionListToReconile, AlfaRomeoEntities context)
        {
            
            // save changes to the transactions.
            context.SaveChanges();

            // HISTORY - Create
            foreach (TransactionMaster transactionMaster in transactionListToReconile)
            {
                TransactionMasterHistory transactionMasterHistory = CreateTransactionMasterHistoryObject(transactionMaster, context);
                context.TransactionMasterHistories.AddObject(transactionMasterHistory);
            }
            // HISTORY - Save
            context.SaveChanges();

            string updatedBy = transactionListToReconile.FirstOrDefault().UpdatedBy;
            DateTime updatedDateTime = transactionListToReconile.FirstOrDefault().UpdatedDate;

            // update bank account statistics
            if (transactionListToReconile.Any(t => t.BankDate.HasValue))
            {
                List<int> reconciledBankIdList = transactionListToReconile.Where(t => t.BankDate.HasValue && t.BankAccountsId.HasValue).Select(r => r.BankAccountsId.Value).Distinct().ToList();
                List<BankAccount> reconciledBanks = context.BankAccounts.Where(t => reconciledBankIdList.Contains(t.Id)).ToList();
                reconciledBanks.ForEach(t => { t.UpdatedBy = updatedBy; t.UpdatedDate = updatedDateTime; });

                UpdateBankAccountDetailsAndStatistics(context);
            }
        }


        public void SaveTransactionMasterList(List<TransactionMaster> tansactionMasterListToSave,         
                                                string scannedImagesRootFolderPath,
                                                AlfaRomeoEntities context)
        {

            Dictionary<string, SequenceNumber> temp = new Dictionary<string, SequenceNumber>();
            // set the voucher and receipt numbers
            for (int i = 0; i < tansactionMasterListToSave.Count; i++)
            {
                TransactionMaster transactionMaster = tansactionMasterListToSave[i];
                FinancialStartEnd financialStartEnd = Utils.GetFinancialYearStartEnd(transactionMaster.TransactionDate);
                SequenceNumber sequenceNumber = null;
                if (!temp.ContainsKey(financialStartEnd.Key))
                {
                    sequenceNumber = GetCurrentSequenceNumbers(context, financialStartEnd.FinancialYearStart, financialStartEnd.FinancialYearEnd);
                    temp.Add(financialStartEnd.Key, sequenceNumber);
                }
                else
                {
                    sequenceNumber = temp[financialStartEnd.Key];
                }


                if (transactionMaster.Id == 0)
                {
                    if (transactionMaster.Head.Equals("head_expense"))
                    {
                        transactionMaster.VoucherReceiptNumber = ++sequenceNumber.LastKnownVoucherNumber;
                    }
                    else
                    {
                        transactionMaster.VoucherReceiptNumber = ++sequenceNumber.LastKnownReceiptNumber;
                    }
                }
                
                // make a corresponding petty cash kitty entries for new  petty cash withdrawal transactions
                if (transactionMaster.IsActive &&
                    transactionMaster.Id == 0 &&
                    transactionMaster.Head.Equals("head_expense") &&
                    transactionMaster.TransactionModeValue.Equals("transaction_mode_pettycashwithdrawal"))
                {
                    PettyCash pettyCash = new PettyCash();
                    pettyCash.Amount = transactionMaster.TransactionAmount;
                    pettyCash.DateWithdrawnFromBank = transactionMaster.TransactionDate;
                    pettyCash.CreatedBy = transactionMaster.CreatedBy;
                    pettyCash.CreatedDate = transactionMaster.CreatedDate;
                    pettyCash.UpdatedBy = transactionMaster.UpdatedBy;
                    pettyCash.UpdatedDate = transactionMaster.UpdatedDate;
                    pettyCash.BalanceAmount = pettyCash.Amount;
                    pettyCash.Narration = transactionMaster.Narration;
                    pettyCash.IsActive = true;
                    transactionMaster.PettyCashes.Add(pettyCash);
                }
            }

            // handle petty cash withdrawal transaction deletion. petty cash needs to be reversed
            List<string> messages = new List<string>();
            messages.AddRange(HandlePettyCashWithdrawalTransactionsEditDelete(tansactionMasterListToSave));
           

            // these are transactions where money was given in expenses
            List<TransactionMaster> pettyCashExpenseTransactions = new List<TransactionMaster>();
            for (int i = 0; i < tansactionMasterListToSave.Count; i++)
            {
                TransactionMaster transactionMaster = tansactionMasterListToSave[i];
                if (transactionMaster.Head.Equals("head_expense") &&
                   transactionMaster.TransactionModeValue.Equals("transaction_mode_cash"))
                {
                    pettyCashExpenseTransactions.Add(transactionMaster);
                    tansactionMasterListToSave.RemoveAt(i);
                    i--;
                }
            }
            if (pettyCashExpenseTransactions.Count > 0)
            {
                 messages.AddRange(SaveCashExpenseTransactionMasterList(pettyCashExpenseTransactions, context));
            }

            // check if cheque expense transactions can be made
            messages.AddRange(HandleChequeExpenseTransactions(tansactionMasterListToSave, context));

            if (messages.Count > 0)
            {
                string consolidatedMessage = string.Join(Environment.NewLine, messages.ToArray());
                throw new DALException(consolidatedMessage);
            }

            // now that all possible checks have been done, set the cheque details inactive for deleted transactions
            foreach (var transactionMaster in tansactionMasterListToSave.Where(t => !t.IsActive))
            {
                foreach (var chequeDetails in transactionMaster.TransactionMasterChequeDetails)
                {
                    chequeDetails.IsActive = false;
                }
            }
            
            Dictionary<string, byte[]> filesToSave = new Dictionary<string, byte[]>();
            foreach (TransactionMaster transactionMaster in tansactionMasterListToSave)
            {
                if (transactionMaster.Id == 0)
                {
                    context.TransactionMasters.AddObject(transactionMaster);
                }

                // save scanned images
                foreach (TransactionMasterChequeDetail transactionMasterChequeDetail in transactionMaster.TransactionMasterChequeDetails.Where(t => t.FileContentChanged))
                {
                    if (!filesToSave.ContainsKey(transactionMasterChequeDetail.ScannedChequeImagePath))
                    {
                        filesToSave.Add(transactionMasterChequeDetail.ScannedChequeImagePath, transactionMasterChequeDetail.FileContent);
                    }
                }
            }

            // Doing this should get the transaction master cheque details id for new entries
            // This is needed in the history
            context.SaveChanges();
            
            // HISTORY - Create
            foreach (TransactionMaster transactionMaster in tansactionMasterListToSave)
            {
                TransactionMasterHistory transactionMasterHistory = CreateTransactionMasterHistoryObject(transactionMaster, context);
                context.TransactionMasterHistories.AddObject(transactionMasterHistory);
            }

            // HISTORY - Create
            // because cash expenses are removed from the main list received in this method
            foreach (TransactionMaster transactionMaster in pettyCashExpenseTransactions)
            {
                TransactionMasterHistory transactionMasterHistory = CreateTransactionMasterHistoryObject(transactionMaster, context);
                context.TransactionMasterHistories.AddObject(transactionMasterHistory);
            }

            // HISTORY - Save
            context.SaveChanges();
            
            if (filesToSave.Count > 0)
            {
                Utils.CopyFiles(scannedImagesRootFolderPath, filesToSave);
            }
        }

        private List<string> HandleChequeExpenseTransactions(List<TransactionMaster> tansactionMasterListToSave, AlfaRomeoEntities context)
        {
            List<string> messages = new List<string>();
            List<BankAccount> bankAccountList = GetAllBankAccounts(Utils.FinancialYear, context);
            List<BankAccount> updatedBankAccountList = SetBankBalances(bankAccountList, context);
            
            Dictionary<int, decimal> intendedChequeExpenditure = new Dictionary<int, decimal>();
            
            List<TransactionMaster> chequeExpenseTransactionList = tansactionMasterListToSave.Where(t =>
                                                                                t.Head.Equals("head_expense") &&
                                                                                t.TransactionModeValue.Equals("transaction_mode_cheque")).ToList();

            foreach (TransactionMaster chequeExpenseTransaction in chequeExpenseTransactionList)
            {
                if (intendedChequeExpenditure.ContainsKey(chequeExpenseTransaction.BankAccountsId.Value))
                {
                    if (chequeExpenseTransaction.IsActive)
                    {
                        intendedChequeExpenditure[chequeExpenseTransaction.BankAccountsId.Value] =
                            intendedChequeExpenditure[chequeExpenseTransaction.BankAccountsId.Value] + chequeExpenseTransaction.TransactionAmount;
                    }
                    else
                    {
                        intendedChequeExpenditure[chequeExpenseTransaction.BankAccountsId.Value] =
                            intendedChequeExpenditure[chequeExpenseTransaction.BankAccountsId.Value] - chequeExpenseTransaction.TransactionAmount;
                    }
                }
                else
                {
                    decimal expenditure = 0;
                    if (chequeExpenseTransaction.IsActive)
                    {
                        expenditure = chequeExpenseTransaction.TransactionAmount;
                    }
                    else
                    {
                        expenditure = -chequeExpenseTransaction.TransactionAmount;
                    }
                    intendedChequeExpenditure.Add(chequeExpenseTransaction.BankAccountsId.Value, expenditure);
                }
            }

            foreach (int bankAccountsId in intendedChequeExpenditure.Keys)
            {
                decimal expenditure = intendedChequeExpenditure[bankAccountsId];
                if (expenditure < 0)
                {
                    continue;
                }
                else
                {
                    BankAccount requiredBankAccount = updatedBankAccountList.Where(t => t.Id.Equals(bankAccountsId)).FirstOrDefault();
                    decimal currentAvailableBalance = requiredBankAccount.AvailableBalance;
                    if (expenditure > currentAvailableBalance)
                    {
                        messages.Add(string.Format("You intend to expend Rs {0} from {1} by way of cheque(s). However, current available balance is Rs {2}. You are falling short by Rs {3}",
                                                      expenditure, requiredBankAccount.BankDisplayName, requiredBankAccount.AvailableBalance, (expenditure - requiredBankAccount.AvailableBalance)));
                                                    
                    }
                }
            }

            return messages;
        }

        private List<string> HandlePettyCashWithdrawalTransactionsEditDelete(List<TransactionMaster> pettyCashWithdrawalTransactionEditedDeletedList)
        {
            List<string> messages = new List<string>();
            foreach (TransactionMaster pettyCashWithdrawalTransactionEditedDeleted in
                            pettyCashWithdrawalTransactionEditedDeletedList.Where(t => t.Id != 0 &&
                                                                                t.Head.Equals("head_expense") && 
                                                                                t.TransactionModeValue.Equals("transaction_mode_pettycashwithdrawal")))
            {
                PettyCash pettyCash = pettyCashWithdrawalTransactionEditedDeleted.PettyCashes.FirstOrDefault();
                // if the withdrawal is being deleted
                if (!pettyCashWithdrawalTransactionEditedDeleted.IsActive)
                {

                    if (pettyCash.PettyCashUsages.Any(t => t.IsActive)) 
                    {
                        messages.Add(string.Format(Environment.NewLine + "Cannot delete the desired petty cash transactions becuase {0} cash expense(s) has been done using the withdrawn money. Please look at the related transactions made against petty cash Id {1}.",
                            pettyCash.PettyCashUsages.Where(t => t.IsActive).ToList().Count.ToString(),
                            pettyCash.Id.ToString()));
                    }
                    else
                    {
                        pettyCash.IsActive = false;
                        foreach (var pettyCashUsage in pettyCash.PettyCashUsages)
                        {
                            pettyCashUsage.IsActive = false;
                        }

                        foreach (var pettyCashChequeDetails in pettyCashWithdrawalTransactionEditedDeleted.TransactionMasterChequeDetails)
                        {
                            pettyCashChequeDetails.IsActive = false;
                        }
                    }
                }
                else // check if the new amount is okay
                {
                    decimal newAmount = pettyCashWithdrawalTransactionEditedDeleted.TransactionAmount;
                    decimal expendedAmount = pettyCash.PettyCashUsages.Sum(t => t.UsedUpAmount);
                    if (expendedAmount > newAmount)
                    {
                        messages.Add(string.Format(Environment.NewLine + "The new amount of Rs {0} for the petty cash transaction is less than what has already been expended against this petty cash withdrawal and which is Rs {1}. Please correct your amount value.",
                                                    newAmount,
                                                    expendedAmount));
                    }
                    else
                    {
                        decimal delta = newAmount - pettyCash.Amount;
                        pettyCash.Amount = newAmount;
                        pettyCash.BalanceAmount = pettyCash.BalanceAmount + delta;
                        pettyCash.DateWithdrawnFromBank = pettyCashWithdrawalTransactionEditedDeleted.TransactionDate;
                        pettyCash.UpdatedBy = pettyCashWithdrawalTransactionEditedDeleted.UpdatedBy;
                        pettyCash.UpdatedDate = pettyCashWithdrawalTransactionEditedDeleted.UpdatedDate;
                        pettyCash.Narration = pettyCashWithdrawalTransactionEditedDeleted.Narration;
                        pettyCash.IsActive = true;

                        if (pettyCash.BalanceAmount == 0)
                        {
                            pettyCash.UsedUpDate = DateTime.Now;
                        }
                    }
                }

            }

            return messages;
        }

        private List<string> SaveCashExpenseTransactionMasterList(List<TransactionMaster> pettyCashExpenseTransactions, AlfaRomeoEntities context)
        {
            List<string> messages = new List<string>();
            decimal totalExpenses = pettyCashExpenseTransactions.Where(r => r.IsActive && r.Id == 0).Sum(t => t.TransactionAmount);

            // cash to be reversed
            foreach (TransactionMaster pettyCashExpenseTransactionMastertoBeDeleted in pettyCashExpenseTransactions.Where(t => !t.IsActive
                                                                        && t.Head.Equals("head_expense")))
            {
                foreach (PettyCashUsage pettyCashUsage in pettyCashExpenseTransactionMastertoBeDeleted.PettyCashUsages)
                {
                    PettyCash pettyCashToBeReversed = context.PettyCashes.Where(t => t.Id.Equals(pettyCashUsage.PettyCashId)).FirstOrDefault();
                    pettyCashToBeReversed.BalanceAmount += pettyCashUsage.UsedUpAmount;
                    
                    pettyCashUsage.IsActive = false; // mark the usage as inactive

                }
            }


            // doing this so that the cash reversal petty cash reflects in the context
            context.SaveChanges();

            decimal availablePettyCash = GetAvailablePettyCash(context); 
            if (totalExpenses > availablePettyCash)
            {
                string message = string.Format("Total cash expense to be saved is Rs {0}.  Available petty cash is Rs {1}." +
                                                " You are falling short by Rs {2}.",
                                                totalExpenses, availablePettyCash, (totalExpenses - availablePettyCash));
                messages.Add(message);

                return messages;
                //throw new DALException(message);
            }

            // Get all non zero balance petty cash
            List<PettyCash> nonZeroPettyCashList = context.PettyCashes.Where(t => t.IsActive && t.BalanceAmount > 0).OrderBy(r => r.CreatedDate).ToList();
            // the id check on 0 is important so that edits do not make a deduction frmo petty cash once again :D
            foreach (TransactionMaster pettyCashExpenseTransactionMaster in pettyCashExpenseTransactions.Where(t => t.IsActive && t.Id == 0))
            {
                decimal cashAmount = pettyCashExpenseTransactionMaster.TransactionAmount;
                List<string> pettyCashIdList = new List<string>();
                
                // make existing petty cash usage entries inactive
                if (pettyCashExpenseTransactionMaster.PettyCashUsages.Count > 0)
                {
                    foreach (var existingPettyCashUsage in pettyCashExpenseTransactionMaster.PettyCashUsages)
                    {
                        existingPettyCashUsage.IsActive = false;
                    }
                }

                bool donePettyCashAccoounting = false;
                for (int i = 0; i < nonZeroPettyCashList.Count; i++)
                {
                    decimal pettyCashUsedUpAmount = 0;
                    if (nonZeroPettyCashList[i].BalanceAmount == 0)
                    {
                        continue;
                    }
                    if (nonZeroPettyCashList[i].BalanceAmount >= cashAmount)
                    {
                        nonZeroPettyCashList[i].BalanceAmount = nonZeroPettyCashList[i].BalanceAmount - cashAmount;
                        pettyCashIdList.Add(nonZeroPettyCashList[i].Id.ToString());
                        if (nonZeroPettyCashList[i].BalanceAmount == 0)
                        {
                            nonZeroPettyCashList[i].UsedUpDate = DateTime.Now;
                        }

                        pettyCashUsedUpAmount = cashAmount;
                        donePettyCashAccoounting = true;
                        //break;
                    }
                    else
                    {
                        cashAmount = cashAmount - nonZeroPettyCashList[i].BalanceAmount;
                        pettyCashUsedUpAmount = nonZeroPettyCashList[i].BalanceAmount;
                        nonZeroPettyCashList[i].BalanceAmount = new decimal(0.00);
                        nonZeroPettyCashList[i].UsedUpDate = DateTime.Now;
                        pettyCashIdList.Add(nonZeroPettyCashList[i].Id.ToString());

                    }

                    PettyCashUsage pettyCashUsage = new PettyCashUsage();
                    pettyCashUsage.IsActive = true;
                    pettyCashUsage.PettyCashId = nonZeroPettyCashList[i].Id;
                    pettyCashUsage.UsedUpAmount = pettyCashUsedUpAmount;
                    pettyCashUsage.CreatedBy = pettyCashExpenseTransactionMaster.CreatedBy;
                    pettyCashUsage.CreatedDate = pettyCashExpenseTransactionMaster.CreatedDate;
                    pettyCashUsage.UpdatedBy = pettyCashExpenseTransactionMaster.UpdatedBy;
                    pettyCashUsage.UpdatedDate = pettyCashExpenseTransactionMaster.UpdatedDate;
                    pettyCashExpenseTransactionMaster.PettyCashUsages.Add(pettyCashUsage);

                    if (donePettyCashAccoounting)
                    {
                        break;
                    }
                }

                pettyCashExpenseTransactionMaster.PettyCashIdList = string.Join(",", pettyCashIdList.ToArray());
                if (pettyCashExpenseTransactionMaster.Id == 0)
                {
                    context.TransactionMasters.AddObject(pettyCashExpenseTransactionMaster);
                }
            }

            context.SaveChanges();
            return messages;
        }

        public void SaveTransactionTypeList(List<TransactionType> transactionTypeListToSave, AlfaRomeoEntities context)
        {
            transactionTypeListToSave.ForEach(t => context.TransactionTypes.AddObject(t));
            context.SaveChanges();
        }

       

        private void RemoveDeletedChildEntriesFromTransactionMaster(List<TransactionMaster> transactionMasterList)
        {
            if (transactionMasterList == null)
            {
                return;
            }
            // since EF does not allow to apply a condition on ".Include"
            foreach (TransactionMaster transactionMaster in transactionMasterList)
            {
                var deletedChequeDetailList = transactionMaster.TransactionMasterChequeDetails.Where(t => !t.IsActive).ToList();
                foreach (TransactionMasterChequeDetail deletedChequeDetail in deletedChequeDetailList)
                {
                    transactionMaster.TransactionMasterChequeDetails.Remove(deletedChequeDetail);
                }
            }
        }

        private TransactionMasterHistory CreateTransactionMasterHistoryObject(TransactionMaster transactionMaster, AlfaRomeoEntities context)
        {
            TransactionMasterHistory transactionMasterHistory = new TransactionMasterHistory();
            transactionMasterHistory.BankAccountsId = transactionMaster.BankAccountsId;
            transactionMasterHistory.BankDate = transactionMaster.BankDate;
            transactionMasterHistory.CreatedBy = transactionMaster.UpdatedBy; // because this is the current logged in user :)
            transactionMasterHistory.CreatedDate = transactionMaster.UpdatedDate;
            transactionMasterHistory.ExpenseApproverIdList = transactionMaster.ExpenseApproverIdList;
            transactionMasterHistory.Head = transactionMaster.Head;
            transactionMasterHistory.HeadDetailsHierarchyIdList = transactionMaster.HeadDetailsHierarchyIdList;
            transactionMasterHistory.HeadDetailsLeafId = transactionMaster.HeadDetailsLeafId;
            transactionMasterHistory.InvoiceOrBillNumber = transactionMaster.InvoiceOrBillNumber;
            transactionMasterHistory.IsActive = true;
            transactionMasterHistory.IsDeleted = !transactionMaster.IsActive;
            transactionMasterHistory.Narration = transactionMaster.Narration;
            transactionMasterHistory.PettyCashIdList = transactionMaster.PettyCashIdList;
            transactionMasterHistory.PONumber = transactionMaster.PONumber;
            transactionMasterHistory.TransactionAmount = transactionMaster.TransactionAmount;
            transactionMasterHistory.TransactionDate = transactionMaster.TransactionDate;
            transactionMasterHistory.TransactionMasterId = transactionMaster.Id;
            transactionMasterHistory.TransactionModeValue = transactionMaster.TransactionModeValue;
            transactionMasterHistory.UpdatedBy = transactionMaster.UpdatedBy;
            transactionMasterHistory.UpdatedDate = transactionMaster.UpdatedDate;
            transactionMasterHistory.VoucherReceiptNumber = transactionMaster.VoucherReceiptNumber;
            transactionMasterHistory.VoucherReceiptPrefix = transactionMaster.VoucherReceiptPrefix;
            transactionMasterHistory.VoucherReceiptPrintedOn = transactionMaster.VoucherReceiptPrintedOn;

            SetChangeDetails(transactionMasterHistory, context);

            if (transactionMaster.TransactionMasterChequeDetails.Count > 0)
            {
                TransactionMasterChequeDetail transactionMasterChequeDetail = transactionMaster.TransactionMasterChequeDetails.First();
                TransactionMasterChequeDetailsHistory transactionMasterChequeDetailsHistory = CreateTransactionMasterChequeDetailsHistoryObject(transactionMasterChequeDetail, context);
                
                // this is very specific to this app. that is, no change and deleted are being ignored
                if (transactionMasterHistory.ChangedFields != transactionMasterChequeDetailsHistory.ChangedFields)
                {
                    transactionMasterHistory.ChangedFields = transactionMasterHistory.ChangedFields + transactionMasterChequeDetailsHistory.ChangedFields;
                }
                
                
                transactionMasterHistory.TransactionMasterChequeDetailsHistories.Add(transactionMasterChequeDetailsHistory);
            }
            
           

            return transactionMasterHistory;
        }

        private TransactionMasterChequeDetailsHistory CreateTransactionMasterChequeDetailsHistoryObject(TransactionMasterChequeDetail transactionMasterChequeDetail, AlfaRomeoEntities context)
        {
            TransactionMasterChequeDetailsHistory transactionMasterChequeDetailsHistory = new TransactionMasterChequeDetailsHistory();
            transactionMasterChequeDetailsHistory.ChequeAmount = transactionMasterChequeDetail.ChequeAmount;
            transactionMasterChequeDetailsHistory.ChequeDate = transactionMasterChequeDetail.ChequeDate;
            transactionMasterChequeDetailsHistory.ChequeDrawnOnBank = transactionMasterChequeDetail.ChequeDrawnOnBank;
            transactionMasterChequeDetailsHistory.ChequeNumber = transactionMasterChequeDetail.ChequeNumber;
            transactionMasterChequeDetailsHistory.ChequeStatus = transactionMasterChequeDetail.ChequeStatus;
            transactionMasterChequeDetailsHistory.CreatedBy = transactionMasterChequeDetail.UpdatedBy;
            transactionMasterChequeDetailsHistory.CreatedDate = transactionMasterChequeDetail.UpdatedDate;
            transactionMasterChequeDetailsHistory.IsActive = true;
            transactionMasterChequeDetailsHistory.IsDeleted = !transactionMasterChequeDetail.IsActive;
            transactionMasterChequeDetailsHistory.Narration = transactionMasterChequeDetail.Narration;
            transactionMasterChequeDetailsHistory.TransactionMasterChequeDetailsId = transactionMasterChequeDetail.Id;
            transactionMasterChequeDetailsHistory.TransactionMasterId = transactionMasterChequeDetail.TransactionMasterId;
            transactionMasterChequeDetailsHistory.UpdatedBy = transactionMasterChequeDetail.UpdatedBy;
            transactionMasterChequeDetailsHistory.UpdatedDate = transactionMasterChequeDetail.UpdatedDate;

            SetChangeDetails(transactionMasterChequeDetailsHistory, context);

            return transactionMasterChequeDetailsHistory;
        }

        private void SetChangeDetails(TransactionMasterHistory toBeSavedTransactionMasterHistory, AlfaRomeoEntities context)
        {
            // get the earlier entry
            TransactionMasterHistory latestExistingTransactionMasterHistory = context.TransactionMasterHistories.Select(t => t).OrderByDescending(t => t.CreatedDate).FirstOrDefault();
            if (latestExistingTransactionMasterHistory == null)
            {
                // a new row
                toBeSavedTransactionMasterHistory.ActionTaken = ActionTaken.Insert.ToString();
                toBeSavedTransactionMasterHistory.ChangedFields = (int)ChangedFields.NoChange;
                return;
            }
            else
            {
                if (!toBeSavedTransactionMasterHistory.IsActive)
                {
                    // deleted
                    toBeSavedTransactionMasterHistory.ActionTaken = ActionTaken.Delete.ToString();

                    // deleted and hence we will not compute changes
                    toBeSavedTransactionMasterHistory.ChangedFields = (int)ChangedFields.Deleted;
                    return;
                }
               
                // an update
                toBeSavedTransactionMasterHistory.ActionTaken = ActionTaken.Update.ToString();

                int changeBitMask = 0;
                // compute change
                // the order of looking for change is not very important. however, better to follow the order of the bit vector
                
                // ChangedFields.BankAccountsId
                if (
                    (latestExistingTransactionMasterHistory.BankAccountsId == null && toBeSavedTransactionMasterHistory.BankAccountsId != null) ||
                    (latestExistingTransactionMasterHistory.BankAccountsId != null && toBeSavedTransactionMasterHistory.BankAccountsId == null) ||
                    (latestExistingTransactionMasterHistory.BankAccountsId.HasValue && toBeSavedTransactionMasterHistory.BankAccountsId.HasValue &&
                     !latestExistingTransactionMasterHistory.BankAccountsId.Value.Equals(toBeSavedTransactionMasterHistory.BankAccountsId.Value))
                   )
                {
                    changeBitMask = changeBitMask + (int)ChangedFields.BankAccountsId;
                }

                //ChangedFields.TransactionModeValue
                if (!latestExistingTransactionMasterHistory.TransactionModeValue.Equals(toBeSavedTransactionMasterHistory.TransactionModeValue))
                {
                    changeBitMask = changeBitMask + (int)ChangedFields.TransactionModeValue;
                }

                //ChangedFields.TransactionDate
                if (!latestExistingTransactionMasterHistory.TransactionDate.Date.Equals(toBeSavedTransactionMasterHistory.TransactionDate.Date))
                {
                    changeBitMask = changeBitMask + (int)ChangedFields.TransactionDate;
                }

                //ChangedFields.BankDate
                if (
                    (latestExistingTransactionMasterHistory.BankDate == null && toBeSavedTransactionMasterHistory.BankDate != null) ||
                    (latestExistingTransactionMasterHistory.BankDate != null && toBeSavedTransactionMasterHistory.BankDate == null) ||
                    (latestExistingTransactionMasterHistory.BankDate.HasValue && toBeSavedTransactionMasterHistory.BankDate.HasValue &&
                     !latestExistingTransactionMasterHistory.BankDate.Value.Date.Equals(toBeSavedTransactionMasterHistory.BankDate.Value.Date))
                   )
                {
                    changeBitMask = changeBitMask + (int)ChangedFields.BankDate;
                }

                //ChangedFields.TransactionAmount
                if (!latestExistingTransactionMasterHistory.TransactionAmount.Equals(toBeSavedTransactionMasterHistory.TransactionAmount))
                {
                    changeBitMask = changeBitMask + (int)ChangedFields.TransactionAmount;
                }

                //ChangedFields.Narration
                if (
                    (!string.IsNullOrEmpty(latestExistingTransactionMasterHistory.Narration) && string.IsNullOrEmpty(toBeSavedTransactionMasterHistory.Narration)) ||
                    (string.IsNullOrEmpty(latestExistingTransactionMasterHistory.Narration) && !string.IsNullOrEmpty(toBeSavedTransactionMasterHistory.Narration)) ||
                    (!string.IsNullOrEmpty(latestExistingTransactionMasterHistory.Narration) && !string.IsNullOrEmpty(toBeSavedTransactionMasterHistory.Narration) &&
                     !latestExistingTransactionMasterHistory.Narration.Equals(toBeSavedTransactionMasterHistory.Narration))
                   )
                {
                    changeBitMask = changeBitMask + (int)ChangedFields.Narration;
                }

                //ChangedFields.HeadDetails
                if (!Utils.IsCommaSeparatedListIdentical(latestExistingTransactionMasterHistory.HeadDetailsHierarchyIdList, toBeSavedTransactionMasterHistory.HeadDetailsHierarchyIdList))
                {
                    changeBitMask = changeBitMask + (int)ChangedFields.HeadDetails;
                }

                //ChangedFields.PettyCashDetails
                if (
                    (!string.IsNullOrEmpty(latestExistingTransactionMasterHistory.PettyCashIdList) && string.IsNullOrEmpty(toBeSavedTransactionMasterHistory.PettyCashIdList)) ||
                    (string.IsNullOrEmpty(latestExistingTransactionMasterHistory.PettyCashIdList) && !string.IsNullOrEmpty(toBeSavedTransactionMasterHistory.PettyCashIdList)) ||
                    (!string.IsNullOrEmpty(latestExistingTransactionMasterHistory.PettyCashIdList) && !string.IsNullOrEmpty(toBeSavedTransactionMasterHistory.PettyCashIdList) && 
                     !Utils.IsCommaSeparatedListIdentical(latestExistingTransactionMasterHistory.PettyCashIdList,toBeSavedTransactionMasterHistory.PettyCashIdList))
                  )
                {
                    changeBitMask = changeBitMask + (int)ChangedFields.PettyCashDetails;
                }


                //ChangedFields.ExpenseApproverList
                if (
                    (!string.IsNullOrEmpty(latestExistingTransactionMasterHistory.ExpenseApproverIdList) && string.IsNullOrEmpty(toBeSavedTransactionMasterHistory.ExpenseApproverIdList)) ||
                    (string.IsNullOrEmpty(latestExistingTransactionMasterHistory.ExpenseApproverIdList) && !string.IsNullOrEmpty(toBeSavedTransactionMasterHistory.ExpenseApproverIdList)) ||
                    (!string.IsNullOrEmpty(latestExistingTransactionMasterHistory.ExpenseApproverIdList) && !string.IsNullOrEmpty(toBeSavedTransactionMasterHistory.ExpenseApproverIdList) &&
                     !Utils.IsCommaSeparatedListIdentical(latestExistingTransactionMasterHistory.ExpenseApproverIdList, toBeSavedTransactionMasterHistory.ExpenseApproverIdList))
                  )
                {
                    changeBitMask = changeBitMask + (int)ChangedFields.ExpenseApproverList;
                }


                if (changeBitMask == 0)
                {
                    changeBitMask = (int)ChangedFields.NoChange;
                }
                toBeSavedTransactionMasterHistory.ChangedFields = changeBitMask;
            }
        }

        private void SetChangeDetails(TransactionMasterChequeDetailsHistory toBeSavedTransactionMasterChequeDetailsHistory, AlfaRomeoEntities context)
        {
            // get the earlier entry
            TransactionMasterChequeDetailsHistory latestExistingTransactionMasterChequeDetailsHistory = 
                    context.TransactionMasterChequeDetailsHistories.Select(t => t).OrderByDescending(t => t.CreatedDate).FirstOrDefault();
            if (latestExistingTransactionMasterChequeDetailsHistory == null)
            {
                // a new row
                toBeSavedTransactionMasterChequeDetailsHistory.ActionTaken = ActionTaken.Insert.ToString();
                toBeSavedTransactionMasterChequeDetailsHistory.ChangedFields = (int)ChangedFields.NoChange;
                return;
            }
            else
            {
                if (!toBeSavedTransactionMasterChequeDetailsHistory.IsActive)
                {
                    // deleted
                    toBeSavedTransactionMasterChequeDetailsHistory.ActionTaken = ActionTaken.Delete.ToString();

                    // deleted and hence we will not compute changes
                    toBeSavedTransactionMasterChequeDetailsHistory.ChangedFields = (int)ChangedFields.Deleted;
                    return;
                }

                // an update
                toBeSavedTransactionMasterChequeDetailsHistory.ActionTaken = ActionTaken.Update.ToString();

                int changeBitMask = 0;
                // compute change
                // the order of looking for change is not very important. however, better to follow the order of the bit vector

                // ChangedFields.ChequeNumber
                if (!latestExistingTransactionMasterChequeDetailsHistory.ChequeNumber.Equals(toBeSavedTransactionMasterChequeDetailsHistory.ChequeNumber))
                {
                    changeBitMask = changeBitMask + (int)ChangedFields.ChequeNumber;
                }

                //ChangedFields.ChequeDate
                if (!latestExistingTransactionMasterChequeDetailsHistory.ChequeDate.Date.Equals(toBeSavedTransactionMasterChequeDetailsHistory.ChequeDate.Date))
                {
                    changeBitMask = changeBitMask + (int)ChangedFields.ChequeDate;
                }

                //ChangedFields.ChequeDrawnOnBank
                if (!latestExistingTransactionMasterChequeDetailsHistory.ChequeDrawnOnBank.Equals(toBeSavedTransactionMasterChequeDetailsHistory.ChequeDrawnOnBank))
                {
                    changeBitMask = changeBitMask + (int)ChangedFields.ChequeDrawnOnBank;
                }

                //ChangedFields.ChequeNarration
                if (
                    (!string.IsNullOrEmpty(latestExistingTransactionMasterChequeDetailsHistory.Narration) && string.IsNullOrEmpty(toBeSavedTransactionMasterChequeDetailsHistory.Narration)) ||
                    (string.IsNullOrEmpty(latestExistingTransactionMasterChequeDetailsHistory.Narration) && !string.IsNullOrEmpty(toBeSavedTransactionMasterChequeDetailsHistory.Narration)) ||
                    (!string.IsNullOrEmpty(latestExistingTransactionMasterChequeDetailsHistory.Narration) && !string.IsNullOrEmpty(toBeSavedTransactionMasterChequeDetailsHistory.Narration) &&
                     !latestExistingTransactionMasterChequeDetailsHistory.Narration.Equals(toBeSavedTransactionMasterChequeDetailsHistory.Narration))
                   )
                {
                    changeBitMask = changeBitMask + (int)ChangedFields.ChequeNarration;
                }


                //ChangedFields.ChequeAmount
                // NOT NEEDED SINCE THIS WILL NEVER BE DIFFERENT THAN THE TRANSACTION DATE
                //if (!latestExistingTransactionMasterChequeDetailsHistory.ChequeAmount.Equals(toBeSavedTransactionMasterChequeDetailsHistory.ChequeAmount))
                //{
                //    changeBitMask = changeBitMask + (int)ChangedFields.ChequeAmount;
                //}

                toBeSavedTransactionMasterChequeDetailsHistory.ChangedFields = changeBitMask;
            }
        }
    }
}
